package com.qlm.poi;
import java.io.File;  
import java.io.FileInputStream;  
import java.io.IOException;  
import java.io.InputStream;  
import java.io.OutputStream;  
import java.lang.reflect.Field;  
import java.lang.reflect.Method;  
import java.text.SimpleDateFormat;  
import java.util.ArrayList;  
import java.util.Arrays;  
import java.util.Date;  
import java.util.Iterator;  
import java.util.List;  
import java.util.Map;  

import org.apache.commons.collections.map.ListOrderedMap;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;  
import org.apache.poi.ss.usermodel.DateUtil;  
import org.apache.poi.ss.usermodel.Row;  
import org.apache.poi.ss.usermodel.Sheet;  
import org.apache.poi.ss.usermodel.Workbook;  
import org.apache.poi.xssf.usermodel.XSSFCell;  
import org.apache.poi.xssf.usermodel.XSSFRow;  
import org.apache.poi.xssf.usermodel.XSSFSheet;  
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  

import com.alibaba.fastjson.JSONObject;

  
/** 
 * Excel工具类，没有硬编码，修改时不要添加硬编码代码 
 * @author WSF 
 * 
 */  
@SuppressWarnings("unchecked")  
public class ExcelUtil {  
      
    private ExcelUtil(){};  
    private static List<String> columns;//要解析excel中的列名  
    private static int sheetNum = 0;//要解析的sheet下标  
    private static boolean dynamicColumn = false;//是否  
    private static Map<String, String> dynamicMapConfig  = new ListOrderedMap();//动态列配置文件  
    /** 
     * poi读取excel 
     * @return 
     */  
    public static String readExcel(File file) throws Exception{  
        StringBuilder retJson = new StringBuilder();  
        InputStream inStream = null; 
        String filePath = file.getPath();
        
        try{
        	if(StringUtils.isBlank(filePath)) {
                throw new IllegalArgumentException("参数错误!!!") ;
            }
            if(filePath.trim().toLowerCase().endsWith("xls")) {
            	inStream = new FileInputStream(file);
            	HSSFWorkbook workbook = new HSSFWorkbook(inStream);
	            HSSFSheet sheet = workbook.getSheetAt(sheetNum);//获得表  
	            int lastRowNum = sheet.getLastRowNum();//最后一行
	            System.out.println("lastRowNum="+lastRowNum);
	            retJson.append("[");  
	            for(int i = 0 ;i < lastRowNum;i++){  
	                HSSFRow row = sheet.getRow(i+1);//获得行  
	                String rowJson = readExcelRow(row);  
	                retJson.append(rowJson);  
	                if(i<lastRowNum-1)  
	                retJson.append(",");  
	            }  
	            retJson.append("]"); 
            } else if(filePath.trim().toLowerCase().endsWith("xlsx")) {
            	  inStream = new FileInputStream(file);
	              XSSFWorkbook workbook = new XSSFWorkbook(inStream);  
	              XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(sheetNum);  
	              int lastRowNum = sheet.getLastRowNum();//最后一行  
	              retJson.append("[");  
	              for(int i = 0 ;i < lastRowNum;i++){  
	                  XSSFRow row = sheet.getRow(i+1);//获得行  
	                  String rowJson = readExcelRow(row);  
	                  retJson.append(rowJson);  
	                  if(i<lastRowNum-1)  
	                      retJson.append(",");  
	              }  
	              retJson.append("]");
            } else {
                 new IllegalArgumentException("不支持除：xls/xlsx以外的文件格式!!!") ;
            }
        }catch(Exception e){
        	e.printStackTrace();
        	throw e;
        }
        return retJson.toString();  
    }  
    /** 
     * poi读取excle 生成实体集合 
     * @param <E> 
     * @return 
     */  
    public static <E> List<E> readExcel(File file,Class<E> clazz) throws Exception{  
        if(columns==null){  
            setColumns(clazz);  
        }  
        InputStream inStream = null;  
        List<E> eList = new ArrayList<E>();  
        try {  
            inStream = new FileInputStream(file);  
            HSSFWorkbook workbook = new HSSFWorkbook(inStream);  
            HSSFSheet sheet = workbook.getSheetAt(sheetNum);//获得表  
            int lastRowNum = sheet.getLastRowNum();//最后一行  
            if(dynamicColumn){  
                //动态列處理  
                HSSFRow header = sheet.getRow(0);//表頭  
                List<String> rList = readRow(header);  
                setDynamicColumn(rList);  
            }  
            for(int i = 0 ;i < lastRowNum;i++){  
                HSSFRow row = sheet.getRow(i+1);//获得行  
                String rowJson = readExcelRow(row);  
                E _e = json2Bean(rowJson,clazz);  
                eList.add(_e);  
            }  
        } catch (Exception e) {  
            try {  
                inStream = new FileInputStream(file);  
                XSSFWorkbook workbook = new XSSFWorkbook(inStream);  
                XSSFSheet sheet = workbook.getSheetAt(sheetNum);  
                int lastRowNum = sheet.getLastRowNum();//最后一行  
                if(dynamicColumn){  
                    //动态列處理  
                    XSSFRow header = sheet.getRow(0);  
                    List<String> rList = readRow(header);  
                    setDynamicColumn(rList);  
                }  
                for(int i = 0 ;i < lastRowNum;i++){  
                    XSSFRow row = sheet.getRow(i+1);//获得行  
                    String rowJson = readExcelRow(row);  
                    E _e = json2Bean(rowJson,clazz);  
                    eList.add(_e);  
                }  
            } catch (Exception e1) {  
                e1.printStackTrace();  
                throw e1;  
            }  
        }finally{  
            close(null,inStream);  
        }  
        return eList;  
    }  
    /** 
     * 动态列配置 
     */  
    private static void setDynamicColumn(List<String> headlist) throws Exception{  
        List<String> tempcolumns = new ArrayList<String>();  
        for (Iterator<String> iterator = headlist.iterator();iterator.hasNext();) {  
            String value = iterator.next();  
            value = value.replace("（", "(");  
            value = value.replace("）", ")");  
            value = value.trim();  
            String key = findKey(value);  
            if(key==null){  
                throw new RuntimeException("请上传合法exce！");  
            }  
            tempcolumns.add(findKey(value));  
        }  
        setColumns(tempcolumns);//重新设置column  
    }  
    /** 
     * 根据value动态找到key值 
     * @return 
     */  
    private static String findKey(String value){  
        for(Map.Entry<String,String> entryconfig:dynamicMapConfig.entrySet()){  
            String keyc = entryconfig.getKey();  
            String valc = entryconfig.getValue();  
            if(valc.equals(value)){  
                return keyc;  
            }  
        }  
        return null;  
    }  
    /** 
     * poi读取excle 生成实体集合 
     * @param file 
     * @param clazz 
     * @param exceptscolumns 
     * @return 
     */  
    public static <E> List<E> readExcel(File file,Class<E> clazz,String[] exceptscolumns) throws Exception{  
        setColumns(clazz,exceptscolumns);  
        return readExcel(file, clazz);  
    }  
    /** 
     * 读取excle多个sheet到多个对象（对象的顺序固定） 
     * @param file 
     * @param clazz 
     * @return 
     */  
    public static <E> List<List<E>> readExcel(File file,Class<E>[] clazz) throws Exception{  
        List<List<E>>  eliLists = new ArrayList<List<E>>();//[clazz.length];  
        int i = 0;  
        for(Class<E> cls:clazz){  
            setColumns(null,null);  
            setSheetNum(i++);  
            List<E> eList = readExcel(file, cls);  
            eliLists.add(eList);  
        }  
        return eliLists;  
    }  
    /** 
     * 将json转换为Bean实例 
     * @param <E> 
     * @return 
     */  
    private static <E> E json2Bean(String json,Class<E> clazz) throws Exception{  
        JSONObject jsonObj = JSONObject.parseObject(json);  
        Method[] methods = clazz.getDeclaredMethods();  
        try {  
            E _e = clazz.newInstance();  
            for(Method m:methods){  
                String name = m.getName();  
                if(name.startsWith("set")){  
                    String keyPrev = name.substring(3,4).toLowerCase();  
                    String keyNext = name.substring(4);  
                    String val = "";  
                    try {  
                        val = jsonObj.getString(keyPrev+keyNext);  
                    } catch (Exception e) {  
                        val = "";  
                    }  
                    m.invoke(_e, val);  
                }  
            }  
            return _e;  
        } catch (Exception e) {  
            e.printStackTrace();  
            throw e;  
        }  
    }  
    /** 
     * 读取行值 
     * @return 
     */  
    private static String readExcelRow(HSSFRow row){  
        StringBuilder rowJson = new StringBuilder();
        System.out.println("row="+row);
        int lastCellNum = row.getPhysicalNumberOfCells();//最后一个单元格  
        System.out.println("lastCellNum="+lastCellNum);
        rowJson.append("{");  
        for(int i = 0 ;i<lastCellNum;i++){  
            HSSFCell cell = row.getCell(i);  
            String cellVal = readCellValue(cell);
            System.out.println("cellVal="+cellVal);
            rowJson.append(toJsonItem(columns.get(i), cellVal));  
            if(i<lastCellNum-1)  
                rowJson.append(",");  
        }  
        rowJson.append("}");  
        return rowJson.toString();  
    }  
    /** 
     * 读取行值 
     * @return 
     */  
    private static String readExcelRow(XSSFRow row){  
        StringBuilder rowJson = new StringBuilder();  
        int lastCellNum = ExcelUtil.columns.size();//最后一个单元格  
        rowJson.append("{");  
        for(int i = 0 ;i<lastCellNum;i++){  
            XSSFCell cell = row.getCell(i);  
            String cellVal = readCellValue(cell);  
            rowJson.append(toJsonItem(columns.get(i), cellVal));  
            if(i<lastCellNum-1)  
                rowJson.append(",");  
        }  
        rowJson.append("}");  
        return rowJson.toString();  
    }  
      
    /** 
     * 读取行生称list 
     * @return 
     */  
    private static List<String> readRow(XSSFRow row){  
        List<String> ret = new ArrayList<String>();  
        int cellcount = row.getLastCellNum();  
        for(int i = 0 ;i < cellcount;i++){  
            XSSFCell cell  = row.getCell(i);  
            String cellval = readCellValue(cell);  
            if(cellval.trim().length()>0){  
                ret.add(cellval);  
            }  
        }  
        return ret;  
    }  
    /** 
     * 读取行生称list 
     * @return 
     */  
    private static List<String> readRow(HSSFRow row){  
        List<String> ret = new ArrayList<String>();  
        int cellcount = row.getLastCellNum();  
        for(int i = 0 ;i < cellcount;i++){  
            HSSFCell cell  = row.getCell(i);  
            String cellval = readCellValue(cell);  
            if(cellval.trim().length()>0){  
                ret.add(cellval);  
            }  
        }  
        return ret;  
    }  
  
    /** 
     * 读取单元格的值 
     * @param hssfCell 
     * @return 
     */  
    @SuppressWarnings("static-access")  
    private static String readCellValue(HSSFCell hssfCell) {  
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {  
            return String.valueOf(hssfCell.getBooleanCellValue());  
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {  
            short format = hssfCell.getCellStyle().getDataFormat();  
            SimpleDateFormat sdf = null;  
            String str_temp = "";  
            if(format == 14 || format == 31 || format == 57 || format == 58){  
                //日期  
                sdf = new SimpleDateFormat("yyyy/MM/dd");  
                double value = hssfCell.getNumericCellValue();  
                Date date = DateUtil.getJavaDate(value);  
                str_temp = sdf.format(date);  
            }else if(format == 10) {  
                //百分比  
                str_temp = hssfCell.getNumericCellValue()+"";  
            }else {  
//                hssfCell.setCellType(1);//设置为String 
            	sdf = new SimpleDateFormat("yyyy/MM/dd");
                str_temp = sdf.format(hssfCell.getDateCellValue());//得到值 
            } 
            System.out.println("str_temp="+str_temp);
            return str_temp;  
        }else if(hssfCell.getCellType() == hssfCell.CELL_TYPE_FORMULA){  
            int val = ((Cell) hssfCell).getCachedFormulaResultType();  
            return val+"";  
        } else {  
            return String.valueOf(hssfCell.getRichStringCellValue());  
        }  
    }  
    /** 
     * 读取单元格的值 
     * @param hssfCell 
     * @return 
     */  
    @SuppressWarnings("static-access")  
    private static String readCellValue(XSSFCell sssfCell) {  
        if (sssfCell.getCellType() == sssfCell.CELL_TYPE_BOOLEAN) {  
            return String.valueOf(sssfCell.getBooleanCellValue());  
        } else if (sssfCell.getCellType() == sssfCell.CELL_TYPE_NUMERIC) {  
            short format = sssfCell.getCellStyle().getDataFormat();  
            SimpleDateFormat sdf = null;  
            String str_temp = "";  
            if(format == 14 || format == 31 || format == 57 || format == 58){  
                //日期  
                sdf = new SimpleDateFormat("yyyy/MM/dd");  
                double value = sssfCell.getNumericCellValue();  
                Date date = DateUtil.getJavaDate(value);  
                str_temp = sdf.format(date);  
            }else if(format == 10) {  
                //百分比  
                str_temp = sssfCell.getNumericCellValue()+"";  
            }else {  
                sssfCell.setCellType(1);//设置为String  
                str_temp = String.valueOf(sssfCell.getRichStringCellValue());//得到值  
            }  
            return str_temp;  
        }else if(sssfCell.getCellType() == sssfCell.CELL_TYPE_FORMULA){  
            int val = sssfCell.getCachedFormulaResultType();  
            return val+"";  
        }else {  
            return String.valueOf(sssfCell.getRichStringCellValue());  
        }  
    }  
    /** 
     * 转换为json对 
     * @return 
     */  
    private static String toJsonItem(String name,String val){  
        return "\""+name+"\":\""+val+"\"";  
    }  
    /** 
     * 关闭io流 
     * @param fos 
     * @param fis 
     */  
    private static void close(OutputStream out, InputStream in) {  
        if (in != null) {  
            try {  
                in.close();  
            } catch (IOException e) {  
                System.out.println("InputStream关闭失败");  
                e.printStackTrace();  
            }  
        }  
        if (out != null) {  
            try {  
                out.close();  
            } catch (IOException e) {  
                System.out.println("OutputStream关闭失败");  
                e.printStackTrace();  
            }  
        }  
    }  
    /** 
     * 填出数据到excel book中 
     * @param book 
     * @param data 
     * @param sheetname 
     * @param titles 
     * @param columns 
     */  
    @SuppressWarnings("rawtypes")
	public static void data2Book(Workbook book,List<? extends Object> data,String sheetname,String[] titles,String[] columns) throws Exception{  
        Sheet sheet = book.createSheet(sheetname);  
        Row th = sheet.createRow((short)0);//标题行  
        for(int i = 0 ;i <titles.length;i++){  
            Cell cell = th.createCell(i);  
            cell.setCellValue(titles[i]);  
        }  
        Object _d = data.get(0);  
        if(_d instanceof Map){  
            //Map集合  
            for (int j = 0;j<data.size();j++) {  
                Map _dm = (Map)data.get(j);  
                Row tr = sheet.createRow((short)(j+1));//内容行  
                for(int k = 0 ;k <columns.length;k++){  
                    Cell cell = tr.createCell(k);  
                    Object val = _dm.get(columns[k]);  
                    String value = val==null?"":val.toString();  
                    cell.setCellValue(value);  
                }  
                  
            }  
        }else {  
            //Bean集合  
            for (int j = 0;j<data.size();j++) {  
                Object _do = data.get(j);  
                Row tr = sheet.createRow((short)(j+1));//内容行  
                for(int k = 0 ;k <columns.length;k++){  
                    String column = columns[k];  
                    Method method = getTargetGetMethod(_do, column);//获取目标方法  
                    try {  
                        Cell cell = tr.createCell(k);  
                        Object val = method.invoke(_do);  
                        String value = val==null?"":val.toString();  
                        value = value.replace("00:00:00.0","");//处理时间中非法字符  
                        cell.setCellValue(value);  
                    } catch (Exception e) {  
                        e.printStackTrace();  
                        throw e;  
                    }  
                }  
                  
            }  
        }  
    }  
    /** 
     * 获取bean的指定getter方法 
     * @param o 
     * @param name 
     * @return 
     */  
    private static Method getTargetGetMethod(Object o,String name) throws Exception{  
        try {  
            String mname  = "get"+name.substring(0,1).toUpperCase()+name.substring(1);  
            return o.getClass().getMethod(mname);  
        } catch (Exception e) {  
            e.printStackTrace();  
            throw  e;  
        }   
    }  
    /** 
     * 将bean所有属性放入map中 
     */  
    private static <E> void beanProp2List(Class<E> clazz,List<String> excepts){  
        Field[] fields = clazz.getDeclaredFields();  
        columns = new ArrayList<String>();//顺序固定可重复  
        for (int i = 0; i < fields.length; i++) {  
            Field field = fields[i];  
            String fieldName = field.getName();  
            if(excepts!=null&&excepts.contains(fieldName))continue;  
            columns.add(fieldName);  
        }  
    }  
      
    public static List<String> getColumns() {  
        return ExcelUtil.columns;  
    }  
    public static void setColumns(List<String> columns) {  
        ExcelUtil.columns = columns;  
    }  
    public static void setColumns(Class<?> clazz){  
        beanProp2List(clazz,null);  
    }  
    /** 
     * 设置列，不包括excepts指定的字段 
     * @param clazz 
     * @param excepts 
     */  
    public static void setColumns(Class<?> clazz,String[] excepts){  
        beanProp2List(clazz,Arrays.asList(excepts));  
    }  
    public static int getSheetNum() {  
        return sheetNum;  
    }  
    public static void setSheetNum(int sheetNum) {  
        ExcelUtil.sheetNum = sheetNum;  
    }  
    public static boolean isDynamicColumn() {  
        return dynamicColumn;  
    }  
    public static void setDynamicColumn(boolean dynamicColumn) {  
        ExcelUtil.dynamicColumn = dynamicColumn;  
    }  
    public static Map<String, String> getDynamicMapConfig() {  
        return dynamicMapConfig;  
    }  
    public static void setDynamicMapConfig(Map<String, String> dynamicMapConfig) {  
        ExcelUtil.dynamicMapConfig = dynamicMapConfig;  
    }  
     
    public static List<String>	getColumnsList(HSSFRow row){
    	ArrayList<String> columnsList = new ArrayList<String>();
//    	int lastCellNum = row.getPhysicalNumberOfCells();
//    	for(int i=0;i<lastCellNum){
//    		
//    	}
    	return columnsList;
    }
    
}  